if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[spFdsDNUpdateProtocols]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[spFdsDNUpdateProtocols]
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

/***************************************************************************
* Name:			spFdsDNUpdateProtocols
* Purpose:		Using table FdsProtocolStagingHumanProtocols, update Protocols tables
*				and other protocol associated tables. This sproc will only update 
*				protocols that have been marked for updates by checking the IsUpdate field.
*
* PARAMETERS
* Name				Description					
* -------------		-----------------------------------------------------------
* n/a
*
* ERRORS USED		Description
* -------------		-----------------------------------------------------
* n/a
*
* RETURN VALUE
* Value    			Description					
* --------------	------------------------------------------------------
* @_exitStatus		An exit status of either true or false
***************************************************************************/

CREATE PROCEDURE dbo.spFdsDNUpdateProtocols
AS
BEGIN

BEGIN TRANSACTION spFdsDNUpdateProtocols

	DECLARE	@SUCCESS	AS BIT
	DECLARE	@FAILURE	AS BIT
	
	DECLARE @_principalInvestigatorStaffTypeId int
	DECLARE @_subsprocStatus	AS BIT
	DECLARE @_UPDATE AS BIT
	DECLARE @_ISINBOUND AS BIT

	SET	@SUCCESS = 1
	SET	@FAILURE = 0
	SET @_principalInvestigatorStaffTypeId = 1
	SET @_UPDATE = 1
	SET @_ISINBOUND = 1

--- Update the PrtProtocol table 
UPDATE PrtProtocol
 SET title = spc.title, 
    ShortTitle = spc.ShortTitle,
    LastModified = spc.LastModified,
    ApprovalDate = spc.ApprovalDate,
    ExpirationDate = spc.ExpirationDate,
    SubmissionDate = spc.SubmissionDate,
    ProtocolNumber = spc.ProtocolNumber,
    PrtProtocolStatusTypeId = spc.PrtProtocolStatusTypeId,
    PrtProtocolSubStatus = spc.PrtProtocolSubStatus,
    PrtProtocolWorkflowStatus = spc.PrtProtocolWorkflowStatus,
    LastModifierAdmPersonId = spc.LastModifierAdmPersonId
FROM PrtProtocol pp
 INNER JOIN FdsProtocolStagingHumanProtocols spc
ON pp.ProtocolNumber = spc.ProtocolNumber
WHERE spc.IsUpdate = @_UPDATE
AND spc.IsInbound = @_ISINBOUND

 IF @@ERROR > 0 GOTO ERROR_HANDLER

-- Update the PI information for protocols that have been marked for update
UPDATE SvcEntity_AdmStaffedPerson_Map
SET AdmPersonId = spc.InitialOwnerAdmPersonId
FROM SvcEntity_AdmStaffedPerson_Map sam
INNER JOIN FdsProtocolStagingHumanProtocols spc
ON sam.SvcEntityId = spc.SvcEntityId
WHERE sam.AdmStaffedPersonTypeId = @_principalInvestigatorStaffTypeId
AND spc.IsUpdate = @_UPDATE
AND spc.IsInbound = @_ISINBOUND	

 IF @@ERROR > 0 GOTO ERROR_HANDLER

-- Update the Staffed person information

UPDATE PrtStaffedPerson
SET FwkOrganizationId = coalesce(psp.PrtFwkOrgId, psp.InitialOwnerFwkPrimaryOrgId, NULL)
FROM SvcEntity_AdmStaffedPerson_Map sam
	INNER JOIN FdsProtocolStagingHumanProtocols psp
	ON sam.svcEntityId = psp.SvcEntityId
	AND sam.AdmPersonId = psp.InitialOwnerAdmPersonId
WHERE psp.IsUpdate = @_NOTUPDATE 
AND psp.IsInbound = @_ISINBOUND
 AND  sam.AdmStaffedPersonTypeId = @_principalInvestigatorStaffTypeId	

 IF @@ERROR > 0 GOTO ERROR_HANDLER

--Insert PIs entries not found
Insert SvcEntity_AdmStaffedPerson_Map (  SvcEntityId, AdmPersonId, AdmStaffedPersonTypeId, DateCreated ) 
SELECT SvcEntityId, InitialOwnerAdmPersonId, @_principalInvestigatorStaffTypeId, getDate()
from FdsProtocolStagingHumanProtocols
WHERE Isupdate = @_UPDATE
AND IsInbound = @_ISINBOUND
     AND  NOT EXISTS ( 
		SELECT 1 FROM SvcEntity_AdmStaffedPerson_Map sam
		INNER JOIN FdsProtocolStagingHumanProtocols spc
		ON sam.SvcEntityId = spc.SvcEntityId
		WHERE sam.AdmStaffedPersonTypeId = @_principalInvestigatorStaffTypeId ) 

 IF @@ERROR > 0 GOTO ERROR_HANDLER

INSERT INTO PrtStaffedPerson(SvcEntityStaffedPersonMapId, HasCertified, FwkOrganizationId)
	SELECT sam.Id, 0, coalesce(psp.PrtFwkOrgId, psp.InitialOwnerFwkPrimaryOrgId, NULL)
	FROM SvcEntity_AdmStaffedPerson_Map sam
	INNER JOIN FdsProtocolStagingHumanProtocols psp
	ON sam.svcEntityId = psp.SvcEntityId
	AND sam.AdmPersonId = psp.InitialOwnerAdmPersonId
	WHERE psp.IsUpdate = @_UPDATE
	AND psp.IsInbound = @_ISINBOUND	    
	AND sam.AdmStaffedPersonTypeId = @_principalInvestigatorStaffTypeId
	 AND  NOT EXISTS ( 
		SELECT 1 FROM SvcEntity_AdmStaffedPerson_Map sam
		INNER JOIN FdsProtocolStagingHumanProtocols spc
		ON sam.SvcEntityId = spc.SvcEntityId
		INNER JOIN PrtStaffedPerson psp
		ON sam.id = psp.SvcEntityStaffedPersonMapId
		WHERE sam.AdmStaffedPersonTypeId = @_principalInvestigatorStaffTypeId ) 


 IF @@ERROR > 0 GOTO ERROR_HANDLER

--- Update the sponsor map information
UPDATE SvcEntity_AdmSponsor_map
SET AdmSponsorId = spc.AdmSponsorId
FROM SvcEntity_AdmSponsor_map sam
	INNER JOIN FdsProtocolStagingHumanProtocols spc
ON sam.SvcEntityId = spc.SvcEntityId
WHERE sam.AdmSponsorRelationTypeId = 1
	AND spc.IsUpdate = @_UPDATE
	AND spc.IsInbound = @_ISINBOUND	

 IF @@ERROR > 0 GOTO ERROR_HANDLER


---Insert sponsor information not found before.

INSERT into SvcEntity_AdmSponsor_map ( SvcEntityId, AdmSponsorId, 
					AdmSponsorRelationTypeId, DateCreated)
    SELECT SvcEntityId, AdmSponsorId, 1 as AdmSponsorRelationTypeId,
      			 getDate() as DateCreated
    FROM FdsProtocolStagingHumanProtocols
    WHERE NOT EXISTS ( SELECT 1 FROM SvcEntity_AdmSponsor_map sam
		INNER JOIN FdsProtocolStagingHumanProtocols spc
		ON sam.SvcEntityId = spc.SvcEntityId
		WHERE sam.AdmSponsorRelationTypeId = 1
	 )
	AND IsUpdate = @_UPDATE AND IsInbound = @_ISINBOUND AND AdmSponsorId is not null

 IF @@ERROR > 0 GOTO ERROR_HANDLER



--Update existing organization map information
UPDATE SvcEntity_FwkDomainOrganization_Map 
SET FwkDomainOrganizationid = coalesce(spc.PrtFwkOrgId, spc.InitialOwnerFwkPrimaryOrgId, 0),
    FwkOrganizationRelationshipTypeId = 1
FROM SvcEntity_FwkDomainOrganization_Map sfm
INNER JOIN FdsProtocolStagingHumanProtocols spc
ON sfm.SvcEntityId = spc.SvcEntityId
WHERE sfm.FwkOrganizationRelationshipTypeId = 1
AND  spc.IsUpdate = @_UPDATE
AND spc.IsInbound = @_ISINBOUND
AND  NOT ( spc.PrtFwkOrgId is  null and spc.InitialOwnerFwkPrimaryOrgId is null)

 IF @@ERROR > 0 GOTO ERROR_HANDLER

--Insert organization information not found before

INSERT SvcEntity_FwkDomainOrganization_Map ( SvcEntityId, FwkDomainOrganizationid,
		 FwkOrganizationRelationshipTypeId, DateCreated)
SELECT SvcEntityId, coalesce(PrtFwkOrgId, InitialOwnerFwkPrimaryOrgId, 0),
		 1 as FwkOrganizationRelationshipTypeId, getDate()
FROM FdsProtocolStagingHumanProtocols
WHERE NOT EXISTS ( SELECT 1 FROM SvcEntity_FwkDomainOrganization_Map sfdm
					  INNER JOIN FdsProtocolStagingHumanProtocols spc
					  ON sfdm.SvcEntityId = spc.SvcEntityId 
					  WHERE sfdm.FwkOrganizationRelationshipTypeId = 1	)
  AND IsUpdate = 1
  AND IsInbound = @_ISINBOUND 
  AND NOT ( PrtFwkOrgId is null and InitialOwnerFwkPrimaryOrgId is null)

   IF @@ERROR > 0 GOTO ERROR_HANDLER 

--- Grant access rights for principals not already given rights before

DECLARE curPIAccessRights CURSOR LOCAL FAST_FORWARD FOR
	SELECT InitialOwnerFwkPrincipalId, FwkResourceId
    from FdsProtocolStagingHumanProtocols spc
	WHERE InitialOwnerFwkPrincipalId is not null 
	AND IsUpdate = @_UPDATE
 	AND IsInbound = @_ISINBOUND
    AND NOT EXISTS ( SELECT 1
					 FROM FwkResource_DomainPrincipal_Map fdm
					 WHERE spc.InitialOwnerFwkPrincipalId = fdm.FwkDomainPrincipalId
					  AND spc.FwkResourceId = fdm.FwkResourceId
					)
	DECLARE @_READ_ACCESS_BITMASK AS INTEGER
	DECLARE @_MANAGE_ACCESS_BITMASK AS INTEGER
	DECLARE @_EXPLICIT_PROVISION_BITVALUE AS INTEGER
	DECLARE @_OWNER_PROVISION_BITVALUE AS INTEGER
		
	SET @_EXPLICIT_PROVISION_BITVALUE = 2
	SET @_OWNER_PROVISION_BITVALUE = 8

	SET @_READ_ACCESS_BITMASK = (SELECT dbo.fnGetAccessRightBitvalueByName ('Read'))
	SET @_MANAGE_ACCESS_BITMASK = (@_READ_ACCESS_BITMASK +
					(SELECT dbo.fnGetAccessRightBitvalueByName('Manage')) +
					(SELECT dbo.fnGetAccessRightBitvalueByName('Edit')) +
					(SELECT dbo.fnGetAccessRightBitvalueByName('Delete')))
	

	DECLARE @_piResourceId AS INTEGER
	DECLARE @_principalId AS INTEGER

	OPEN curPIAccessRights 
	FETCH NEXT FROM curPIAccessRights INTO @_principalId ,  @_piResourceId

	WHILE @@FETCH_STATUS = 0
	BEGIN

		EXEC @_subsprocStatus = spFwkNGrantAccess  @_principalId ,  @_piResourceId,
					  @_OWNER_PROVISION_BITVALUE, @_MANAGE_ACCESS_BITMASK

		
		IF @_subsprocStatus = @FAILURE GOTO ERROR_HANDLER

		FETCH NEXT FROM curPIAccessRights INTO @_principalId,  @_piResourceId
		
	END

	
	-- close and deallocate cursor
	CLOSE curPIAccessRights
	DEALLOCATE curPIAccessRights 


SET NOCOUNT OFF
COMMIT TRANSACTION  spFdsDNUpdateProtocols
RETURN @SUCCESS

ERROR_HANDLER: 
  SET NOCOUNT OFF
  ROLLBACK TRANSACTION spFdsDNUpdateProtocols
 RETURN @FAILURE

END

GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

